﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class DAOMonAn:DataProvider
    {
        public static DataTable getAll()
        {
            string select = "select * from monan";
            var da = new SqlDataAdapter(select, KetNoi());
            DataTable dt = new DataTable();
            da.Fill(dt);
            DongKetNoi();
            return dt;
        }

        public static DataTable getAll(object mamonan = null, object tenmonan = null)
        {                        
            StringBuilder select = new StringBuilder("select * from monan where 1=1");
            var da = new SqlDataAdapter(select.ToString(),KetNoi());
            if ((string)mamonan != "")
            {
                select.Append(" and mamonan LIKE @mamonan");
                da.SelectCommand.Parameters.AddWithValue("@mamonan","%"+(string)mamonan+"%");
            }
            if ((string)tenmonan != "")
            {
                select.Append(" and tenmonan LIKE @tenmonan");
                da.SelectCommand.Parameters.AddWithValue("@tenmonan", "%"+(string)tenmonan+"%");
            }
            da.SelectCommand.CommandText = select.ToString();
            
            DataTable dt = new DataTable();
            da.Fill(dt);
            DongKetNoi();
            return dt;
        }

        public static int add(DTOMonAn obj,DataTable dtt)
        {
            string select = "select * from monan";
            var da = new SqlDataAdapter(select, KetNoi());
            new SqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            var row = dt.NewRow();
            row["GiaTien"] = obj.GiaTien;
            row["MaMonAn"] = obj.MaMonAn;
            row["MaNV"] = obj.MaNV;
            row["MaQG"] = obj.MaQG;
            row["TenMonAn"] = obj.TenMonAn;            
            dt.Rows.Add(row);
            int kq = da.Update(dt);
            DAOChiTietMonAn.save(dtt, obj.MaMonAn);
            DongKetNoi();
            return kq;
        }

        public static int edit(DTOMonAn obj, string old_ma)
        {
            string select = "select * from monan";
            var da = new SqlDataAdapter(select, KetNoi());
            new SqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);

            var row = dt.Rows.Find(old_ma);
            row.BeginEdit();
            row["GiaTien"] = obj.GiaTien;
            row["MaMonAn"] = obj.MaMonAn;
            row["MaNV"] = obj.MaNV;
            row["MaQG"] = obj.MaQG;
            row["TenMonAn"] = obj.TenMonAn;
            row.EndEdit();

            DongKetNoi();
            return da.Update(dt);
        }

        public static int del(DTOMonAn obj)
        {
            string select = "select * from monan";
            var da = new SqlDataAdapter(select, KetNoi());
            new SqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);

            var row = dt.Rows.Find(obj.MaMonAn);
            row.Delete();

            DongKetNoi();
            return da.Update(dt);
        }

        public static bool checkDuplicate(DTOMonAn obj, string old_ma)
        {
            var da = new SqlDataAdapter();
            if (old_ma == null)
            {
                string select = "select * from monan where mamonan=@mamonan";
                da = new SqlDataAdapter(select, KetNoi());
                da.SelectCommand.Parameters.AddWithValue("@mamonan", obj.MaMonAn);                
            }
            else
            {
                string select = "select * from monan where mamonan=@mamonan and mamonan!=@old_ma";
                da = new SqlDataAdapter(select, KetNoi());
                da.SelectCommand.Parameters.AddWithValue("@mamonan", obj.MaMonAn);
                da.SelectCommand.Parameters.AddWithValue("@old_ma", old_ma);
            }
            
            new SqlCommandBuilder(da);
            DataTable dt = new DataTable();            
            da.Fill(dt);

            bool ret = dt.Rows.Count > 0 ? true : false;
            DongKetNoi();
            return ret;
        }
    }
}
